﻿using System;
using System.Collections.Generic;
using IndoReputation.Models.BusinessObject;
using System.Data.SqlClient;
using System.Data;
using IndoReputation.Models.Constants;
using IndoReputation.Models.BusinessObject.Collections;

namespace IndoReputation.Models.DataAccess
{
    public class UsersDAL
    {
        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:39 AM
        /// Todo: Insert a Object User to Database
        /// </summary>
        /// <param name="user">A user.</param>
        /// <returns></returns>
        public static int Insert(Users user)
        {
            try
            {
                const string spName = "Users_Insert";
                var spPara = new SqlParameter[13];
                spPara[0] = new SqlParameter("@ID", SqlDbType.Int, 2, ParameterDirection.Output, false, 0, 0, null, DataRowVersion.Default, null);
                spPara[1] = new SqlParameter("@OfficeID", user.OfficeID);
                spPara[2] = new SqlParameter("@LoginName", user.LoginName);
                spPara[3] = new SqlParameter("@Password", user.Password);
                spPara[4] = new SqlParameter("@FullName", user.FullName);
                spPara[5] = new SqlParameter("@Telephone", user.Telephone);
                spPara[6] = new SqlParameter("@MemoryWord", user.MemoryWord);
                spPara[7] = new SqlParameter("@LastLogin", DBNull.Value);
                spPara[8] = new SqlParameter("@CreateAt", user.CreateAt);
                spPara[9] = new SqlParameter("@CreateBy", user.CreateBy);
                spPara[10] = new SqlParameter("@UpdateAt", DBNull.Value);
                spPara[11] = new SqlParameter("@UpdateBy", DBNull.Value);
                spPara[12] = new SqlParameter("@IsEnable", user.IsEnable);                
                DataAccessBase.ExecuteNonQuery(CommandType.StoredProcedure, spName, spPara);
                return Convert.ToInt32(spPara[0].Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  9:45 AM
        /// Todo: Update a User Exists in Database 
        /// </summary>
        /// <param name="user">A user.</param>
        /// <returns></returns>
        public static bool Update(Users user)
        {
            try
            {
                string spName = "Users_UpdateByPK";
                SqlParameter[] spPara = new SqlParameter[13];
                spPara[0] = new SqlParameter("@ID", user.ID);
                spPara[1] = new SqlParameter("@OfficeID", user.OfficeID);
                spPara[2] = new SqlParameter("@LoginName", user.LoginName);
                spPara[3] = new SqlParameter("@Password", user.Password);
                spPara[4] = new SqlParameter("@FullName", user.FullName);
                spPara[5] = new SqlParameter("@Telephone", user.Telephone);
                spPara[6] = new SqlParameter("@MemoryWord", user.MemoryWord);
                spPara[7] = new SqlParameter("@LastLogin", user.LastLogin);
                spPara[8] = new SqlParameter("@CreateAt", user.CreateAt);
                spPara[9] = new SqlParameter("@CreateBy", user.CreateBy);
                spPara[10] = new SqlParameter("@UpdateAt", user.UpdateAt);
                spPara[11] = new SqlParameter("@UpdateBy", user.UpdateBy);
                spPara[12] = new SqlParameter("@IsEnable", user.IsEnable);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                return totalRecordEffected > 0;
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 12/8/2011  2:02 PM
        /// Todo: update login time
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public static bool UpdateLoginTime(Users user)
        {
            try
            {
                string spName = "[Users_UpdateLoginTimeByPK]";
                SqlParameter[] spPara = new SqlParameter[2];
                spPara[0] = new SqlParameter("@ID", user.ID);
                spPara[1] = new SqlParameter("@LastLogin", user.LastLogin);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 12/8/2011  2:02 PM
        /// Todo: update password
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public static bool UpdatePassword(Users user)
        {
            try
            {
                string spName = "Users_UpdatePasswordByPK";
                SqlParameter[] spPara = new SqlParameter[4];
                spPara[0] = new SqlParameter("@ID", user.ID);
                spPara[1] = new SqlParameter("@Password", user.Password);
                spPara[2] = new SqlParameter("@UpdateAt", user.UpdateAt);
                spPara[3] = new SqlParameter("@UpdateBy", user.UpdateBy);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 12/8/2011  2:02 PM
        /// Todo: update active and admin
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public static bool UpdateActAdmin(Users user)
        {
            try
            {
                const string spName = "Users_UpdateActiveAndAdminByPK";
                var spPara = new SqlParameter[5];
                spPara[0] = new SqlParameter("@ID", user.ID);
                spPara[1] = new SqlParameter("@IsEnable", user.IsEnable);
                spPara[2] = new SqlParameter("@IsAdmin", user.IsAdmin);
                spPara[3] = new SqlParameter("@UpdateAt", user.UpdateAt);
                spPara[4] = new SqlParameter("@UpdateBy", user.UpdateBy);
                var totalRecordEffected = 0;
                var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                return totalRecordEffected > 0;
            }
            catch (Exception)
            {
                return false;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 12/8/2011  2:02 PM
        /// Todo: update lock many users
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns></returns>
        public static bool UpdateLockMany(bool status, string Ids)
        {
            try
            {
                string spName = "Users_UpdateLockManyByPK";
                SqlParameter[] spPara = new SqlParameter[2];
                spPara[0] = new SqlParameter("@status", status);
                spPara[1] = new SqlParameter("@xml", Ids);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:36 AM
        /// Todo: Delete a User Existst in Database
        /// </summary>
        /// <param name="user">A user.</param>
        /// <returns></returns>
        public static bool Delete(int userID)
        {
            try
            {
                string spName = "Users_DeleteByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", userID);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:36 AM
        /// Todo: Delete a User Existst in Database
        /// </summary>
        /// <param name="user">A user.</param>
        /// <returns></returns>
        public static bool DeleteMany(string Ids)
        {
            try
            {
                string spName = "Users_DeleteManyByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@xml", Ids);
                int totalRecordEffected = 0;
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                totalRecordEffected = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
                            }
                        }
                    }
                }
                if (totalRecordEffected > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception Ex)
            {
                return false;
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static UsersList GetAllUsers()
        {
            try
            {
                UsersList listUsers = null;
                string spName = "Users_SelectAll";
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listUsers = new UsersList();
                                List<Users> listTemp = DataAccessBase.ConvertToList<Users>(ds.Tables[0]);
                                foreach (Users _r in listTemp)
                                    listUsers.Add(_r);
                            }
                        }
                    }
                }
                return listUsers;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static List<Users> GetAllUsersAdmin()
        {
            List<Users> listUsers = null;
            const string spName = "Users_SelectAllAdmin";
            var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0] != null)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            listUsers = ds.Tables[0].ConvertToList<Users>();                                
                        }
                    }
                }
            }
            return listUsers;
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:40 AM
        /// Todo: Select a User existst in Database
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public static Users GetUserById(int userID)
        {
            try
            {
                Users user = null;
                string spName = "Users_SelectByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", userID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                user = DataAccessBase.ConvertToEntity<Users>(ds.Tables[0].Rows[0]);
                            }
                        }
                    }
                }
                return user;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static Users GetUserByNameAndWord(string loginName, string memoryWord)
        {
            try
            {
                Users user = null;
                const string spName = "Users_SelectByNameAndMemoryword";
                var spPara = new SqlParameter[2];
                spPara[0] = new SqlParameter("@LoginName", loginName);
                spPara[1] = new SqlParameter("@MemoryWord", memoryWord);
                var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                user = ds.Tables[0].Rows[0].ConvertToEntity<Users>();
                            }
                        }
                    }
                }
                return user;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        public static UsersExtend GetUserExtendById(int userID)
        {
            try
            {
                UsersExtend user = null;
                string spName = "Users_SelectAndCountryRegionByPK";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", userID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                user = DataAccessBase.ConvertToEntity<UsersExtend>(ds.Tables[0].Rows[0]);
                            }
                        }
                    }
                }
                return user;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 12/2/2011  3:01 PM
        /// Todo: Check login
        /// </summary>
        /// <param name="loginName">Name of the user.</param>
        /// <param name="password">The password.</param>
        /// <returns></returns>
        public static IRConstants.UserStatus GetUserByNameAndPass(string loginName, string password)
        {
            try
            {
                var userStatus = IRConstants.UserStatus.NotExists;
                const string spName = "Users_SelectByNameAndPass";
                var spPara = new SqlParameter[2];
                spPara[0] = new SqlParameter("@LoginName", loginName);
                spPara[1] = new SqlParameter("@Password", password);
                var ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                if (Convert.ToBoolean(ds.Tables[0].Rows[0]["IsEnable"]))
                                {
                                    userStatus = IRConstants.UserStatus.UnLock;
                                    MySession.UserID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]);
                                    MySession.LoginName = ds.Tables[0].Rows[0]["LoginName"].ToString();
                                    MySession.FullName = ds.Tables[0].Rows[0]["FullName"].ToString();
                                    MySession.IsAdmin = (ds.Tables[0].Rows[0]["IsAdmin"] != null) && (bool)ds.Tables[0].Rows[0]["IsAdmin"];
                                    var users = new Users
                                                    {
                                                        ID = Convert.ToInt32(ds.Tables[0].Rows[0]["ID"]),
                                                        LastLogin = DateTime.Now
                                                    };
                                    UpdateLoginTime(users);
                                }
                                else
                                {
                                    userStatus = IRConstants.UserStatus.Lock;
                                }
                            }
                        }
                    }
                }
                return userStatus;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/29/2011  5:23 PM
        /// Todo: Get All Users
        /// </summary>
        /// <returns></returns>
        public static UsersJoinList GetAllUsersJoin()
        {
            try
            {
                UsersJoinList listUsers = null;
                string spName = "Users_SelectAllJoin";
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, null);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                listUsers = new UsersJoinList();
                                List<UsersJoin> listTemp = DataAccessBase.ConvertToList<UsersJoin>(ds.Tables[0]);
                                foreach (UsersJoin _r in listTemp)
                                    listUsers.Add(_r);
                            }
                        }
                    }
                }
                return listUsers;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 11/30/2011  10:40 AM
        /// Todo: Select a User existst in Database
        /// </summary>
        /// <param name="userID">The user ID.</param>
        /// <returns></returns>
        public static UsersCheck GetUserCEOAndEnableById(int userID)
        {
            try
            {
                UsersCheck user = null;
                string spName = "Users_SelectCEOAndEnable";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@ID", userID);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count > 0)
                            {
                                user = DataAccessBase.ConvertToEntity<UsersCheck>(ds.Tables[0].Rows[0]);
                            }
                        }
                    }
                }
                return user;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }

        /// <summary>
        /// Author: Nguyen Duc Thanh Tung
        /// Created on: 12/2/2011  3:01 PM
        /// Todo: Check login
        /// </summary>
        /// <param name="loginName">Name of the user.</param>
        /// <param name="password">The password.</param>
        /// <returns></returns>
        public static bool CheckDuplicateLoginName(string loginName)
        {
            try
            {
                bool result = false;
                string spName = "Users_SelectByName";
                SqlParameter[] spPara = new SqlParameter[1];
                spPara[0] = new SqlParameter("@LoginName", loginName);
                DataSet ds = DataAccessBase.ExecuteDataset(CommandType.StoredProcedure, spName, spPara);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0] != null)
                        {
                            if (ds.Tables[0].Rows.Count == 0)
                            {
                                result = true;
                            }
                            else
                                result = false;
                        }
                    }
                }
                return result;
            }
            catch (Exception Ex)
            {
                throw Ex;
            }
        }
    }
}